package com.flex.mobile.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Service;

import com.flex.mobile.dao.IGenericDAO;
import com.flex.mobile.dao.model.DriverInfo;
import com.flex.mobile.util.SqlUtil;

@Service("driverInfoDao")
public class DriverInfoDao implements IGenericDAO<DriverInfo> {
	private final SimpleJdbcTemplate template;

	private final SimpleJdbcInsert insertContact;

	@Autowired
	public DriverInfoDao(DataSource dataSource) {
		this.template = new SimpleJdbcTemplate(dataSource);
		this.insertContact = new SimpleJdbcInsert(dataSource).withTableName("DRIVER_INFO").usingGeneratedKeyColumns("ID");
	}

	private final RowMapper<DriverInfo> summaryRowMapper = new ParameterizedRowMapper<DriverInfo>() {

		@Override
		public DriverInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			DriverInfo item = new DriverInfo();
			item.setId(rs.getInt("id"));
			item.setTid(rs.getLong("T_ID"));
			item.setUserName(rs.getString("USERNAME"));
			item.setPassword(rs.getString("PASSWORD"));
			item.setCarId(rs.getString("CAR_ID"));
			item.setCompany(rs.getString("COMPANY"));
			item.setCompanyId(rs.getString("COMPANY_ID"));
			item.setTelephone(rs.getString("TELEPHONE"));
			item.setRegistDate(rs.getDate("REGISTER_DATE"));
			return item;
		}
	};

	@Override
	public DriverInfo create(DriverInfo item) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		String sql = "select nextval('UserSeq')";
		int cid = this.template.queryForInt(sql, new Object[] {});
		item.setTid(cid);
		parameters.put("ID", null);
		parameters.put("T_ID", cid);
		parameters.put("USERNAME", item.getUserName());
		parameters.put("PASSWORD", item.getPassword());
		parameters.put("CAR_ID", item.getCarId());
		parameters.put("COMPANY", item.getCompany());
		parameters.put("COMPANY_ID", item.getCompanyId());
		parameters.put("TELEPHONE", item.getTelephone());
		Number id = this.insertContact.executeAndReturnKey(parameters);
		item.setId(id.intValue());
		return item;
	}

	@Override
	public List<DriverInfo> findAll() {
		return this.template.query("SELECT * FROM DRIVER_INFO ", this.summaryRowMapper);
	}
	public List<DriverInfo> findByTidList(List<Long>tidList) {
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT * FROM DRIVER_INFO where t_id ").append(SqlUtil.inSql(tidList.size()));
		return this.template.query(sql.toString(), this.summaryRowMapper,tidList.toArray());
	}
	@Override
	public DriverInfo findById(long id) {
		String sql = "select * from DRIVER_INFO e " + "where e.id = ? ";
		return this.template.queryForObject(sql, summaryRowMapper, id);
	}

	@Override
	public List<DriverInfo> findByName(String userName) {
		String sql = "select * from DRIVER_INFO e " + "where e.USERNAME  like ?";
		return this.template.query(sql, summaryRowMapper, "%" + userName.trim() + "%");
	}
	
	public List<DriverInfo> findByNameOrPhone(String userNameOrPhone) {
	   String sql = "select * from DRIVER_INFO e " + "where e.USERNAME  like ? or e.TELEPHONE = ?";
      return this.template.query(sql, summaryRowMapper, "%" + userNameOrPhone.trim() + "%", userNameOrPhone.trim());
	}

	@Override
	public boolean remove(DriverInfo item) {
		int count = this.template.update("DELETE FROM DRIVER_INFO WHERE id=?", item.getId());
		return count == 1;
	}

	@Override
	public boolean update(DriverInfo item) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE DRIVER_INFO SET PASSWORD=?, CAR_ID=?, COMPANY=?, COMPANY_ID=?, TELEPHONE=? ");
		sql.append(" WHERE id=?");
		int count = this.template.update(sql.toString(), item.getPassword(), item.getCarId(), item.getCompany(), item.getCompanyId(), item.getTelephone(), item.getId());
		return count == 1;
	}

}
